package managers.socialNetworkManagers;

import java.net.URL;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.sql.Timestamp;
import java.util.ArrayList;
import java.util.Calendar;

import javax.swing.ImageIcon;

import managers.DBManager;
import spotGUI.ElementViewer.ElementView;
import utils.ImageUtils;
import utils.SocialUserData;
import elements.Comment;
import elements.Element;
import elements.Vote;

public class ElementsManager {

	public static final int ALL_USERS = -1;
	public static final int ALL_TYPES = -1;
	public static final Timestamp ALWAYS = null;



	public static URL retrieveTypeIcon(int type) {
		URL headerImagePath = null;
		switch (type) {
		case Element.POST:
			headerImagePath = ElementView.class.getResource(
					"/images/ElementsIcons/message.png");
			break;
		case Element.PHOTO:
			headerImagePath = ElementView.class.getResource(
					"/images/ElementsIcons/photo.png");
			break;
		case Element.PERSONAL_MESSAGE:
			headerImagePath = ElementView.class.getResource(
					"/images/ElementsIcons/personal.png");

			break;
		}
		return headerImagePath;
	}




	public static void addElement (Connection conn, Element element) throws SQLException {
		String query = "insert into spotmessenger.ELEMENTS (type, senderid, title, body, photo) values (?, ?, ?, ?, ?)";
		PreparedStatement stmt = conn.prepareStatement(query);
		stmt.setInt(1, element.type);
		stmt.setInt(2, element.userId);
		stmt.setString(3, element.name);
		stmt.setString(4, element.body);
		ImageUtils.imageToDB(element.photo, stmt, 5);
		stmt.executeUpdate();
		DBManager.closeResources(stmt, null);
	}


	private static boolean firstRestriction;
	
	public static ArrayList<Element> retrieveElements (Connection conn, Timestamp from, Timestamp to, int userId, int type, int quantity) throws SQLException {

		firstRestriction = true;

		ArrayList<Element> elements = new ArrayList<Element>();

		Statement stmt = conn.createStatement();

		String query = "select * from spotmessenger.ELEMENTS EL";
		if (from != ALWAYS) {
			query = checkFirstRestriction(query);
			query += "sendtime>='"+from+"'";
		}
		if (to != ALWAYS ) {
			query = checkFirstRestriction(query);
			Calendar cal = Calendar.getInstance();
			cal.setTime(to);
			cal.set(Calendar.HOUR_OF_DAY, 23);
			cal.set(Calendar.MINUTE, 59);
			cal.set(Calendar.SECOND, 59);
			query += "sendtime<='"+new Timestamp(cal.getTime().getTime())+"'";
		}
		if (userId != ALL_USERS) {
			query = checkFirstRestriction(query);
			query += "senderid="+userId;
		}
		if (type != ALL_TYPES) {
			query = checkFirstRestriction(query);
			query += "type="+type;
		}
		query+= " order by GREATEST(coalesce((select MAX(COMM.SENDTIME) from spotmessenger.comments COMM where COMM.elementid = EL.elementid), 0), sendtime) desc limit "+quantity;
		ResultSet rs = stmt.executeQuery(query);
		while (rs.next()) {
			SocialUserData sender = SocialNetworkManager.getSharedInstance().getUser(rs.getInt(3));
			elements.add(new Element(rs.getInt(1), rs.getInt(2), rs.getInt(3), rs.getTimestamp(4),
					rs.getString(5), rs.getString(6), ImageUtils.bytesToImage(rs.getBytes(7), null),
					sender.getPhoto(), sender.getFirstName()+" "+sender.getSurname()));
		}
		DBManager.closeResources(stmt, rs);
		return elements;
	}




	private static String checkFirstRestriction(String query) {
		if (!firstRestriction) {
			query += " and ";
		} else {
			query += " where ";
			firstRestriction = false;
		}
		return query;
	}


	public static ArrayList<Comment> retrieveComments (Connection conn, int elementId) throws SQLException {
		Statement stmt = conn.createStatement();
		String query = "select C.*, U.firstname, U.surname, U.photominiature from spotmessenger.COMMENTS C, spotmessenger.USERS u where C.elementid="+elementId+" and" +
				" C.senderid = U.id";
		ResultSet rs = stmt.executeQuery(query);
		ArrayList<Comment> comments = new ArrayList<Comment>();
		while (rs.next()) {
			comments.add(new Comment(rs.getInt(1), rs.getInt(2), rs.getString(5)+" "+rs.getString(6), ImageUtils.bytesToImage(rs.getBytes(7), "/images/Icons/32x32/user.png"),
					rs.getTimestamp(3), rs.getString(4)));
		}
		DBManager.closeResources(stmt, rs);
		return comments;
	}


	public static ArrayList<Vote> retrieveVotes (Connection conn, int elementId) throws SQLException {
		Statement stmt = conn.createStatement();
		String query = "select V.*, U.firstname, U.surname, U.photo from spotmessenger.VOTES V, spotmessenger.USERS U where elementid="+elementId+" and" +
				" U.id = V.senderid";
		ResultSet rs = stmt.executeQuery(query);
		ArrayList<Vote> votes = new ArrayList<Vote>();
		while (rs.next()) {
			votes.add(new Vote(rs.getInt(3), rs.getInt(1), rs.getString(4)+" "+rs.getString(5),
					ImageUtils.bytesToImage(rs.getBytes(6), "/images/Icons/32x32/user.png"), rs.getInt(2)));
		}
		DBManager.closeResources(stmt, rs);
		return votes;
	}



	public static void comment (Connection conn, int elementId, int senderId, String text) throws SQLException {
		Statement stmt = conn.createStatement();
		String query = "insert into spotmessenger.comments (elementid, senderid, body) values ("+elementId+", "+senderId+", '"+text.replace("'", "\\'")+"')";
		stmt.executeUpdate(query);
		DBManager.closeResources(stmt, null);
	}

	public static void vote (Connection conn, int elementId, int senderId, int vote) throws SQLException, com.mysql.jdbc.exceptions.jdbc4.MySQLIntegrityConstraintViolationException {
		Statement stmt = conn.createStatement();
		String query = "insert into spotmessenger.votes values ("+senderId+", "+vote+", "+elementId+")";
		stmt.executeUpdate(query);
		DBManager.closeResources(stmt, null);
	}


}
